<?php

include 'DBLibrary/config.php';
include 'DBLibrary/openDB.php';

mysql_select_db($dbname);

require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

$rowedit = mysql_fetch_array(mysql_query("SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 8 HOUR),'%y-%m-%d-%h-%i-%s') as date"));

// sending HTTP headers
$workbook->send('Student-' . $rowedit['date'] . '.xls');

// Creating a worksheet
$worksheet = & $workbook->addWorksheet('Student List');

//format of text
$format_left = & $workbook->addFormat();
$format_left->setAlign('left');
$format_left->setAlign('middle');
$format_left->setFontFamily('Calibri');
$format_left->setSize('11');

$format_center_bold = & $workbook->addFormat();
$format_center_bold->setAlign('center');
$format_center_bold->setAlign('middle');
$format_center_bold->setBold('1');
$format_center_bold->setFontFamily('Calibri');
$format_center_bold->setSize('13');

/////////////////////////////////SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 8 HOUR),'%y-%m-%d-%h-%i-%s')

$querystud = "select * from student where validate='activated' order by lname, fname";
$allstud = mysql_query($querystud);
$ctr = 0;
$numrows = mysql_num_rows($allstud);

//column width
$worksheet->setColumn(0, 0, 7);
$worksheet->setColumn(1, 1, 15);
$worksheet->setColumn(2, 3, 20);
$worksheet->setColumn(4, 4, 15);
$worksheet->setColumn(5, 5, 25);
$worksheet->setColumn(6, 6, 30);

$worksheet->write(0, 0, 'No', $format_center_bold);
$worksheet->write(0, 1, 'Student ID', $format_center_bold);
$worksheet->write(0, 2, 'Last Name', $format_center_bold);
$worksheet->write(0, 3, 'First Name', $format_center_bold);
$worksheet->write(0, 4, 'Gender', $format_center_bold);
$worksheet->write(0, 5, 'Telephone Number', $format_center_bold);
$worksheet->write(0, 6, 'Email Address', $format_center_bold);

for ($x = 1; $x <= $numrows; $x++) {
    $worksheet->write($x, 0, $x, $format_center);
}
$ctr = 1;
while ($row = mysql_fetch_array($allstud)) {
    $worksheet->write($ctr, 1, $row['studidno'], $format_left);
    $worksheet->write($ctr, 2, $row['lname'], $format_left);
    $worksheet->write($ctr, 3, $row['fname'], $format_left);
    $worksheet->write($ctr, 4, $row['gender'], $format_left);
    $worksheet->write($ctr, 5, $row['telno'], $format_left);
    $worksheet->write($ctr, 6, $row['email'], $format_left);
    $ctr++;
}

$workbook->close();
include 'DBLibrary/closeDB.php';
?>